Archive Magazine CD 1995/Archive Magazine CD 1995.iso/discs/pipeline/abacus/p_line/DataBase2/ReadMe

Text File | 1993-02-26 | 10.4 KB | 205 lines

%OP%VS4.13 (28-Apr-92), Gerald L Fitton, R4000 5966 9904 9938
%OP%DP0
%OP%IRY
%OP%PL0
%OP%HM0
%OP%FM0
%OP%BM0
%OP%LM4
%OP%PT1
%OP%PDPipeLine
%OP%WC1026,2258,184,1748,0,0,0,0
%CO:A,72,72%
%C%A Simple DataBase - Part 2
%C%by Gerald L Fitton
Keywords:
Beginners database Fitton

Introduction
In DataBase1 I described what is perhaps the simplest of databases.
Each record uses one row in a PipeDream document and has values for
every one of five 'Fields'.  In this directory I shall describe what I
think is an instructive (but not the best) way of adding a new record
and how to use the database to generate 'form letters'.  I shall also
show one way of producing labels using dependent documents leaving a
second method (using a parameter file) for another occasion.

Surprisingly I haven't received a file of "E-Y-B" ("Eligible Young
Bachelors") yet so the file is called [Girls02] and it is based on the
file, [Girls01] from directory DataBase1.  You can find a copy of
[Girls02] in this directory.  Just as you did with DataBase1, print out
[Girls02].

Start with [Girls01] from the DataBase1 directory and modify it by
inserting a few rows in the places shown in [Girls02].  All the
formulae will change appropriately.  The five fields are: 'Name', 'Hair
Colour', 'Eye Colour', 'Character' and 'Favourite Present' for each of
the eight (fictitious) young ladies.

Adding Rows
Perhaps the simplest way of adding a row is to place the cursor
somewhere in the middle of the data and tap F7 to insert a row.  All
the formulae in row 7 will change to match the enlarged database.  Type
in the data and, if you want to, you can sort the database again on any
column or columns.  You can also delete a record using F8 to delete a
whole row.  What you must not do is delete either of the two blank rows
which bracket the data (ie the ones referenced in the formulae).  In
the file [Girls02] these are rows 8 and 17.

An alternative way of entering data is by copying a master row.  The
master row is row 4.  Type in data such as that shown.  Now, using the
mouse, place the cursor on the 4 of row 4 (ie in the margin or
'border') and double click.  Row 4 will become highlighted ('marked' as
in 'marked block').  Now use the mouse to position the cursor anywhere
in column A within the data base (A9 to A16), click once and then copy
the marked block (Ctrl BC).  Finally, delete the word "Master:" (using
F11) and you can go back to the master row (row 4) and modify the data
for the next record.

Generally, this is a reasonably good method if you have a lot of
fields, many of which don't alter from record to record.  It is
possible to write a macro which will mark the master row, copy the
record to the database and bring the cursor back to the master row
ready for the next data entry - but that is a technique for experts.

A Form Letter
Rows 19 to 21 contain a "Form Letter".  A form letter is usually a
letter sent to all (or many of) the people whose names are in the
database.  Often the fields are names and addresses, money owed, prizes
you might have won, etc, but we have other more attractive
characteristics of our eligible young ladies.  The letter is typed in
with lots of @@ characters bracketing cell references from row 7 as:

@@C7@@, I thought of you; I remembered your beautiful @@E7@@ eyes, your
@@D7@@ hair and your @@F7@@ character. I decided I had to get you
@@G7@@.

Note that the cell references do not have to appear in the form letter
in column (or any other) order; any reference can appear anywhere in
the form letter.  If you want extra space for a long field then add
extra @@ signs behind the cell reference such as @@C7@@@@@@@@@@@@@.

If Sandy is typed into B7 then all values in row 7 will change to pick
up the values from the database (the block B9 to F16), this is followed
by the form letter picking up the cell references so that lines 19 to
21 will read:

Sandy, I thought of you; I remembered your beautiful blue eyes, your
auburn hair and your fiery character. I decided I had to get you
a sports car.

Mark rows 19 to 21 and (from the Print menu) Print marked block.  The
references will be evaluated before printing.  You have printed a "Form
Letter"!  By changing the value in cell B7 to say, Liz, you can send a
similar customised letter to Liz!  Try it now.

Labels
The most usual use of linking a database to a label generating
application is to produce address labels.  My example isn't for address
labels.  It uses the database of E-Y-Ls; perhaps you want to label
boxes of mementoes of the times you have spent together - photos, cds,
restaurant bills, etc.  Although there are many ways of producing
labels from a database, essentially these fall into two classes; these
are: (a) using dependent documents or (b) using parameter files.  I
shall deal only with (a) on this disc.

Labels Using Dependent Documents
Before you produce a set of labels you may want to sort the file so
that you can print a selection of the labels rather than all of them.
I prefer to have the labels I want to print at the bottom of the
database but you might prefer them at the top.  You can complicate the
formulae in the label generating document and not sort the database but
I don't want to explain how to do that at the moment.

From this directory, load the file [Label01].  For the purpose of this
tutorial I have assumed that you have a single column of labels with
each label having two columns.  I have also assumed that the vertical
distance from the top of one label to the next is 10 PipeDream lines.
If these values are unsuitable for your labels then you can modify the
file accordingly.

The advantage of using 10 rows per label for this tutorial is that each
label starts on a row such as 1, 11, 21, 31, etc and the printing
occupies lines 4 to 8, 14 to 18, etc. This makes it a little easier for
us 'denary' thinkers to see what is going on (eg the first 10 labels
use lines 1 to 99 inclusive).  By the way, I reckon that for most data
transfer problems it's better to consider that computers 'think' in a
scale of 256 (expressed as a two digit hex number - a byte) rather than
in the binary code taught so extensively in most elementary text books
on computing - sixteen ounces one pound, sixteen pounds one byte?

The cell [Label01]B4 contains the value of the key field for the first
label.  You can type in 'Jane' (the value) or the cell reference
[Girls02]B9.  The [Girls02] part of this cell reference indicates that
the value you want is in the dependent document called [Girls02].  Make
sure that [Girls02] is loaded or PipeDream might not find the file.

If you are repeating the creation of [Label01] then tap <F2> (the
function key which gives Edit Function) and type the formula in
carefully remembering to enter the $ signs where they are shown.  When
you press <Return> the word 'blond' will appear in cell B5.

Mark cells B5 to B8 and replicate with <Ctrl BRD> (Block Replicate
Down).  The result will be 'blond' in all four cells.  There are many
good ways of modifying the 'faulty' formulae but here's an instructive
one.  Place the cursor in cell [Label01]B6 and call up the sub menu
<Ctrl BSE> (Block SEarch).  In the dialogue box 'String to search for'
enter C and in the 'Replace with' with D.  PipeDream will not change
the C to a D in the formula unless you turn ON 'Search expression
slots' by clicking the mouse in that dialogue box.  Repeat this for
cells B7 and B8 changing the Cs to Es and Fs respectively.

Note that B14 appears in the formulae for the second label where B4
appears in the first - otherwise the formulae are identical.

Move the cursor to cell [Label01]B14 and type in the formula.  If you
have not done so already tap F2 to convert the text to a formula.  This
lookup formula finds Jane in the range B8B17 and then returns the value
found in the corresponding place in the range B9B18 (ie one record down
the database); the value returned is Janet.  Try it first and then
think about it!

If it is not still marked then mark the block [Label01]B5B8.  Place the
cursor in [Label01]B15 and then <Ctrl BRE> to replicate the block B5B8
to B15B18.  During this replication the B4s will change to B14s but
(because of the $s) all the rest of the lookup formulae will be fixed.
If you have done all this correctly then you will get, not the correct
formulae but the second label.

You can replicate the block A11B20 down the column of labels as far as
you wish.  Generally the quickest way of doing this is by doubling the
block size at every replication - this way it takes 10 replications to
produce 1024 labels.  Generally it doesn't matter if the label
generator is too long - you can always delete some of it.  Save this
master label generator.

Before printing your labels you can mark the whole [Label01] document
and use <Ctrl BSS> (Block SnapShot) to convert all the formulae to
values (but don't Save this over the top of the [Label01] file that
contains the formulae or you will lose these formulae and have to start
again).  This snapshotted file is editable as a plain text file so you
can delete individual labels, add columns, move blocks around, etc, and
see exactly what you have got before finally printing.  Particularly
with RISC OS drivers you can do useful things such as change the font,
change the line spacing or change the print scale factor.

If all your lookup formulae give values which are aligned right and you
want them aligned to the left margin then you can change this.  Double
click on the B at the top of the B column to mark the whole B column,
then give the command <Ctrl LAL> (Layout Align Left).

Once you have the [Label01] file you can use it with databases other
than [Girls02].  For example, suppose you want to use it with [Girls03]
(your own - not on this disc) then you could rename [Girls02] to
something else and then rename [Girls03] as [Girls02].  I use a variant
of this method.  My label file is used with files called [Addresses1],
[Addresses2], [Addresses3], etc; I make a copy (using Copy) of the
wanted file calling the copy [Addresses].  [Addresses] is the name of
my dependent document in my [Label] generator.  That way I can use the
same label generator document with many different address files.

A way of changing all the Girls02 to Girls is to mark the whole of your
label file and follow this with <Ctrl BSE> (Block SEarch) to replace
[Girls02] with, say, [Girls] (remember to turn 'Search expression
slots' ON).  Save the new label generator as [Label].